-- @owner: lwx1120110
-- @date: 2023-02-18
-- @testpoint: 验证四则运算结合事务输出

--step1:创建表;expect:建表成功
set dolphin.b_compatibility_mode='on';
drop table if exists t_operator_0063;
create table t_operator_0063
(col11 tinyint,col12 smallint,col13 integer,col14 bigint
,col15 float4,col16 float8,col17 decimal,col18 numeric,col19 bit(4)
,col21 char(3),col22 varchar(20)
,col23 binary(5),col24 varbinary(20)
,col25 tinyblob,col26 blob
,col27 mediumblob,col28 longblob
,col29 enum('521','51.0','5.0','974.1')
,col210 set('1','11','100','887.11')
,col211 text,col212 json
,col31 date,col32 datetime,col33 timestamp,col34 time
,col35 year,col36 datetime(6),col37 timestamp(6));

--step2:插入数据;expect:插入成功
insert into t_operator_0063 values
(7,30,8,10,8.516,10.247,553.6124,735.8111,B'1101',
'7.1','31.768','3.017','5347','961.1','999'::blob,
'332.1','3570','5.0','100','2','1.00'::json,
'3587-01-01','5361-07-11 01:01:29','339-05-28','23:00:00',
'2024','2001-07-15 02:05:33.4421','665-09-03 22:23:24.11111'),
(52,19,21,112,97.31,40.227,918.989,537.251,B'1010',
'2.5','31.768','2.581','942.3698','3321.475','2243'::blob,
'3327','240578.1121','521','1','2','2.00'::json,
'2020-06-15','2020-05-31 17:22:24','2010-12-12','09:37:11',
'1996','2020-05-31 17:22:24.253164','1995-03-26 05:15:15.71'),
(11,31,27,105,9.271,345.374,3700.624,188.881,B'1110',
'0.1','2.1587','32.47','53.12','221','707'::blob,'53.12',
'912.55','51.0','11','23.7','0.04'::json,
'331-06-27','4417-05-06 14:31:09','100-01-02','23:59:59',
'2039','1772-11-16 09:22:36.447','9654-03-29 23:10:11.886'),
(80,119,211,312,97.831,0.227,0.9989,7.251,B'1011',
'0.9','53.42','551.1','4.021','3.112','684'::blob,'3.27',
'10.211','974.1','887.11','31.13','25.71'::json,
'220-03-15','2222-11-30 22:22:24','9999-12-31','23:59:59',
'1991','1-11-11 11:11:11.33689','1998-06-26 07:15:36.1158');

--step3:混合运算结合事务1;expect:成功
start transaction;
drop table if exists t_operator_0063_01;
select 
(col11+col21)*col31-col12/(col22*col31)*col13^2/col23/(col35-col14)+col24*col210 c1,
col15^2/col25-col13*(col16+col26-col18)*col17/col27-col35*col18+col28 c2,
-col19*col29+col27/col18*col210*col35-col17-col211+col35/col212*col31+col16 c3 
from t_operator_0063
into t_operator_0063_01;
end;

--step4:查询表1数据;expect:查询成功
select * from t_operator_0063_01;

--step5:混合运算结合事务2;expect:成功
begin;
drop table if exists t_operator_0063_02;
select 
(-col19+col27)*col212/(col18-col31)*col211/col17-col210^2/(col35+col16)*col29 c1,
-col15*col28/(col31-col14)*col27+col35-col13*col26/col31*(col12+col23-col25) c2,
col11*col24-col31*(col12/col23^2)*(1/2)+col13*col22-col35/(col21-col15) c3 
from t_operator_0063
into t_operator_0063_02;
end;

--step6:查询表2数据;expect:查询成功
select * from t_operator_0063_02;

--step7:清理环境;expect:成功
drop table if exists t_operator_0063_01;
drop table if exists t_operator_0063_02;
drop table if exists t_operator_0063 cascade;
reset dolphin.b_compatibility_mode;